SQL

常用sql语句整理

什么是SQL?

  • SQL是一种结构化查询语言(structured query language)
  • 我们用它来访问数据库

others

  • RDBMS 关系型数据库管理系统(relational database management system)
  • 大小写不敏感
  • 可分为两部分：
  • 数据操作语言(DML)：SELECT、UPDATE、DELETE、INSERT、INTO
  • 数据定义语言(DDL)：CREATE DATABASE、ALTER DATABASE、
    CREATE TABLE、ALTER TABLE、DROP TABLE、CREATE INDEX、DROP INDEX

select语句

1
2
3
SELECT * FROM tablename
SELECT columnname FROM tablename
SELECT name1,name2 FROM tablename

  • DISTINCT

    1
    SELECT DISTINCT columname FROM tablename -从表里面返回 不重复 的columnam值

  • WHERE

    1
    2
    3
    SELECT columnname from tablename WHERE columname = value
    - "="可以换为 <>(不等于),>,<,>=,<=,between(某个范围内)
    - value 如果是文本，需要加单引号，如果是数值，直接写

  • AND OR

    1
    2
    有多个限定条件时可以用and或or连接，分别是&&和||的作用。
    SELECT * FROM tablename where a=1 AND b=2

  • ORDER BY
    DESC:descend降序；ASC：ascend升序；默认升序

    1
    2
    3
    SELECT * FROM tablename ORDER BY columname DESC
    SELECT columname1,columname2 FROM tablename ORDER BY columname 
    SELECT columname1,columname2 FROM tablename ORDER BY columname1,columname2

INSERT INTO

1
2
INSERT INTO tablename VALUES(v1,v2,v3)
INSERT INTO tablename(c1,c2,c3) VALUES(v1,v2,v3)

UPDATE

1
2
UPDATE tablename SET columnname=value WHERE columnname=value
UPDATE tablename SET columnname1=value1,columnname2=value2 WHERE columnname=value

DELETE

1
2
DELETE FROM tablename WHERE columname=value
DELETE * FROM tablename 不删除表的情况下删除所有行

TOP

1
SELECT TOP 3 * FROM tablename  返回前三条数据

TOP PERCENT

1
SELECT TOP 50 PERCENT * FROM tablename 返回前50%的数据

LIKE

1
2
3
4
5
SELECT * FROM tablename WHERE col LIKE 'N%' 返回col的值以N开头的记录
SELECT * FROM tablename WHERE col LIKE '%ooo%'
% 代表模式中缺少的字母

SELECT * FROM tablename WHERE col NOT LIKE '%ooo%
  • %:代替一个或多个字符
  • _:替代一个字符

IN

Id LastName FirstName Address City
1 Adams John Oxford Street London
2 Bush George Fifth Avenue New York
3 Carter Thomas Changan Street Beijing

1
2
SELECT * FROM Persons
WHERE LastName IN ('Adams','Carter')

Id LastName FirstName Address City
1 Adams John Oxford Street London
3 Carter Thomas Changan Street Beijing

Alias/AS 取别名

1
2
SELECT LastName AS Family, FirstName AS Name
FROM Persons

Family Name
Adams John
Bush George
Carter Thomas

JOIN

1
2
3
4
5
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
INNER JOIN Orders
ON Persons.Id_P = Orders.Id_P
ORDER BY Persons.LastName

UNION 连接结果集

1
2
3
SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2

CREATE TABLE

1
2
3
4
5
6
7
8
9
10
CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
UNIQUE(Id_P),
PRIMARY KEY (Id_P)
)
1
2
3
4
5
6
7
8
9
CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
FOREION KEY (Id_P) REFFERENCES Persons(Id_P)
)

CHECK约束

1
2
3
4
5
6
7
8
CREATE TABLE Persons
(
Id_P int NOT NULL CHECK (Id_P>0),
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

欢迎与我分享你的看法。
转载请注明出处：http://taowusheng.cn/
微博：寒枫–0-0–
知乎：https://www.zhihu.com/people/tao-wu-sheng
豆瓣：YIFEI